package sys.database;

import java.sql.*;

public class SysbDatabase
{
    static public int maxRequestID(String DBHost, String DBName, String user, String pass) throws SQLException
    {
        Connection con = connect(DBHost, DBName, user, pass);
        ResultSet rs = null;
        int maxID = 0;

        Statement statement = con.createStatement();
        String query =  "SELECT MAX(ID) as ID FROM Request";
        rs = statement.executeQuery(query);

        rs.next();
        maxID = rs.getInt("ID");

        statement.close();
        disconnect(con);
        
        return maxID; 
    }

    static private Connection connect(String DBHost, String DBName, String user, String pass)
    {
        Connection con = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager.getConnection("jdbc:mysql://" + DBHost + "/" + DBName, user, pass);

            if(!con.isClosed())
                System.out.println("Successfully connected to dbname = " + DBName + ", user = " + user + "MySQL server using TCP/IP...");
        }
        catch(Exception e)
        {
            System.err.println("Error while connection to the database: " + e.getMessage());
        }
        return con;
    }


    static private void disconnect(Connection con)
    {
        try
        {
            con.close();
        }
        catch(Exception e)
        {
            System.err.println("Error while disconnection from the database: " + e.getMessage());
        }
    }

    static private void createBuyerTable(Connection con, String DBHost, String DBName) throws SQLException 
    {
            Statement statement = con.createStatement();
            String query =  "CREATE TABLE " + DBName + ".Buyer (" +
                            "Address char(50) NOT NULL," +
                            "Password char(50) NOT NULL, PRIMARY KEY  (Address)" + 
                            ") ENGINE=InnoDB DEFAULT CHARSET=utf8";
            statement.executeUpdate(query);
            statement.close();
    }

    static private void createPayingSystemTable(Connection con, String DBHost, String DBName) throws SQLException
    {
            Statement statement = con.createStatement();
            String query =  "CREATE TABLE " + DBName + ".PayingSystem (" +
                            "Password char(50) NOT NULL," +
                            "Address char(50) NOT NULL, PRIMARY KEY  (Address)" + 
                            ") ENGINE=InnoDB DEFAULT CHARSET=utf8";
            statement.executeUpdate(query);
            statement.close();
    }

    static private void createRequestTable(Connection con, String DBHost, String DBName) throws SQLException
    {
            Statement statement = con.createStatement();
            String query =  
            "CREATE TABLE " + DBName + ".Request" +
            "(" + 
            "ID INT NOT NULL," + 
            "IDOnBuyer INT NOT NULL," +
            "Buyer_Address CHAR(50) NOT NULL," +
            "User_Name CHAR(50) NOT NULL," +
            "PayingSystem_Address CHAR(50) NOT NULL," +
            "Quantity INT NOT NULL," +
            "Payed INT NOT NULL DEFAULT '0'," +
            "AckByBuyer INT NOT NULL DEFAULT '0'," +

            "CONSTRAINT Buyer_Address FOREIGN KEY Buyer_Address(Buyer_Address)" +
            "	REFERENCES Buyer(Address)" +
            "	ON DELETE RESTRICT" +
            "	ON UPDATE RESTRICT," +

            "CONSTRAINT PayingSystem_Address FOREIGN KEY PayingSystem_Address(PayingSystem_Address)" +
            "	REFERENCES PayingSystem(Address)" +
            "	ON DELETE RESTRICT" +
            "	ON UPDATE RESTRICT," +
            "PRIMARY KEY (ID)" +
            ")" +
            "ENGINE=InnoDB DEFAULT CHARSET=utf8; ";

            statement.executeUpdate(query);
            statement.close();
    }

    static private void createStatisticsTable(Connection con, String DBHost, String DBName) throws SQLException
    {
            Statement statement = con.createStatement();
            String query =  
            "CREATE TABLE " + DBName + ".Statistics" +
            "(" + 
            "Buyer_Address CHAR(50) NOT NULL," +
            "User_Name CHAR(50) NOT NULL," +
            "Bad INT NOT NULL," + 
            "Good INT NOT NULL," + 
            "PRIMARY KEY (Buyer_Address, User_Name)" +
            ")" +
            "ENGINE=InnoDB DEFAULT CHARSET=utf8; ";

            statement.executeUpdate(query);
            statement.close();
    }

    static public void createTables(String DBHost, String DBName, String user, String pass)
    {
        Connection con = connect(DBHost, DBName, user, pass);

        try 
        {
            createBuyerTable(con, DBHost, DBName);
        } 
        catch(Exception e) 
        {
            System.err.println("Error while creating table 'User': " + e.getMessage());
        }

        try 
        {
            createPayingSystemTable(con, DBHost, DBName);
        } 
        catch(Exception e) 
        {
            System.err.println("Error while creating table 'Paying system': " + e.getMessage());
        }

        try 
        {
            createRequestTable(con, DBHost, DBName);
        } 
        catch(Exception e) 
        {
            System.err.println("Error while creating table 'Paying system': " + e.getMessage());
        }

        try 
        {
            createStatisticsTable(con, DBHost, DBName);
        } 
        catch(Exception e) 
        {
            System.err.println("Error while creating table 'Statistics': " + e.getMessage());
        }

        disconnect(con); 
    }

    static private void dropTable(Connection con, String DBHost, String DBName, String tableName) throws SQLException
    {
        Statement statement = con.createStatement();
        String query =  "DROP TABLE IF EXISTS " + DBName + "." + tableName;
        statement.executeUpdate(query);
        statement.close();
    }

    static public void dropTables(String DBHost, String DBName, String user, String pass)
    {
        Connection con = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager.getConnection("jdbc:mysql://" + DBHost + "/" + DBName, user, pass);

            if(!con.isClosed())
                System.out.println("Successfully connected to dbname = " + DBName + ", user = " + user + "MySQL server using TCP/IP...");
        }
        catch(Exception e)
        {
            System.err.println("Error while connection to the database: " + e.getMessage());
        }

        try 
        {
            dropTable(con, DBHost, DBName, "Request");
            dropTable(con, DBHost, DBName, "Buyer");
            dropTable(con, DBHost, DBName, "PayingSystem");
            dropTable(con, DBHost, DBName, "Statistics");
        } 
        catch(Exception e) 
        {
            System.err.println("Error while dropping tables: " + e.getMessage());
        }
    }
}
